#This R script calculates the dealer-level and average Treasury collateral multipliers (CMs) for the paper "Treasury Re-use and the Demand for Safe Assets" by Sebastian Infante and Zack Saravay. 
#The program consists of four functions. The code to pull the FR2052a Complex Institution Liquidity Monitoring Report data is omitted, along with certain aspects of data cleaning, because the dealer-level data is confidential. The first function applies initial cleaning to the data. The second calculates the aggregate SFT volumes shown in Figure 3 in the paper, The third calculates the dealer-level all contracts and repo CMs and their components, which are the dependent variables in our main analysis.  The fourth calculates cross-sectional averages of the all-contract and repo CMs, which are shown in Figure 4 in the paper.
#The dealer level CMs and their components are the used as the independent variables in our main analysis, which is carried out in treasuryReuseMain.do

library(dplyr)

#################################
#### Pull and anonymize data ####
#################################

##########################
#### Initial Cleaning ####
##########################

#This function cleans the FR2052a data. It cuts off the data for a selected  start and end date and removes unsettled transactions
cleanFR2052Data <- function(x, startDate = "2016-01-01", endDate = Sys.Date(), removeUnsettled = TRUE, primaryDealerAdjust = TRUE){
      
     print(paste("Cleaning", deparse(substitute(x)), "..."))
     
     x <- as.data.frame(subset(x, date <= endDate & date >= startDate))
 

     #Remove unsettled transactions
     if(removeUnsettled) {
          #subset where forward start is zero or NA, which represents settled transactions
          x <- subset(x, fwd_start == 0 | is.na(fwd_start))
          print("Forward starts removed")
     }


return(x)
}

############################
#### Aggregate SFT Data ####
############################

#This function aggregates the FR2052 outflows and inflows data. It calculates the total amount of incoming and outgoing SFTs and the amount of rehypothecated and  encumbered SFTs, then outputs them to a CSV. These series are shown in Figure 3 in the paper.

aggSft <- function(outflows,inflows,assets) {

     #Aggregate total outflows across all contracts and dealers
     aggOutflows <- as.data.frame(outflows
                                  %>% filter(internal_ind == "external")
                                  %>% group_by(date)
                                  %>% summarise(outgoing = sum(collateral_value, na.rm = TRUE)))

     #Aggregate rehypothecated outflows across all contracts and dealers
     aggRehy <- as.data.frame(outflows
                                  %>% filter(internal_ind == "external" & rehypothecated_ind == TRUE)
                                  %>% group_by(date)
                                  %>% summarise(rehyOutgoing = sum(collateral_value, na.rm = TRUE)))

     #Aggregate rehypothecated outflows across all contracts and dealers
     aggNonRehy <- as.data.frame(outflows
                                  %>% filter(internal_ind == "external" & rehypothecated_ind == FALSE)
                                  %>% group_by(date)
                                  %>% summarise(nonRehyOutgoing = sum(collateral_value, na.rm = TRUE)))

     #Aggregate total inflows across all contracts and dealers
     aggInflows <- as.data.frame(inflows
                                  %>% filter(internal_ind == "external")
                                  %>% group_by(date)
                                  %>% summarise(incomingSft = sum(collateral_value, na.rm = TRUE)))

     #Aggregate encumbered inflows across all contracts and dealers
     aggEnc <- as.data.frame(inflows
                                  %>% filter(internal_ind == "external" & unencumbered_ind == "N")
                                  %>% group_by(date)
                                  %>% summarise(encIncomingSft = sum(collateral_value, na.rm = TRUE)))

      #Aggregate total unencumbered assets across all contracts and dealers
     aggUnencAssets <- as.data.frame(assets
                                  %>% group_by(date)
                                  %>% summarise(unencAssets = sum(market_value, na.rm = TRUE)))

     #Merge SFT series
     aggSft <- Reduce(function(x,y) merge(x,y, all = TRUE), list(aggInflows, aggEnc, aggOutflows, aggRehy, aggNonRehy, aggUnencAssets))

     #Calculate total incoming collateral as total incomingSFTs + non-rehypothecated outflows (proxy for encumbered assets) + unencumbered assets
     aggSft$incoming <- aggSft$incomingSft + aggSft$nonRehyOutgoing + aggSft$unencAssets

     write.csv(aggSft, "aggregateSFTVolumes.csv")
}



####################################
#### Calculate Dealer-Level CMs ####
####################################

#This function calculates the all collateral and repo collateral multpliers at the date-dealer level. First, it sums the outflows data at the date-dealer level to calculate the numerators and demoninator of the two CMs. Only external transactions are included. Second, it calculates the CMs by dividing each numerator by the denominator. Finally, it outputs the anonymized dealer-level CMs and their componenets in a csv file.

dealerLevelCm <- function(outflows) {

     #### Calculate CM numerators ####
     
     #Sum the collateral value of external treasury SFT outflows (all contracts)for each date and dealer
     allContractsDealer <- as.data.frame(outflows
                                  %>% filter(internal_ind == "external")
                                  %>% group_by(DealerID, date)
                                  %>% summarise(allContracts = sum(collateral_value, na.rm = TRUE)))

     #Sum the collateral value of external treasury repo for each date and dealer
     repoDealer <- as.data.frame(outflows
                           %>% filter(product == "Repo" & internal_ind == "external")
                           %>% group_by(DealerID, date)
                           %>% summarise(repo = sum(collateral_value, na.rm = TRUE)))

     
     #### Calculate CM denominator ####

     #Sum the collateral value of external treasury non-rehypothecated SFT outflows (all contracts) for each date and dealer
     nonRehyAllContractsDealer <- as.data.frame(outflows %>% filter(rehypothecated_ind == FALSE & internal_ind == "external") %>% group_by(DealerID, date) %>% summarise(nonRehyAllContracts = sum(collateral_value, na.rm = TRUE)))

     #### Calculate dealer-level multipliers ####

     #Merge numerators and denominator of CM at date-dealer level
     cmDealer <- Reduce(function(x,y) merge(x,y, all = TRUE), list(allContractsDealer, repoDealer, nonRehyAllContractsDealer))
     #Replace NAs with zeroes
     cmDealer[is.na(cmDealer)] <- 0

     #Calculate all contracts CM as total SFT outflows divided by non-rehypothecated SFT outflows
     cmDealer$USText <- cmDealer$allContracts / cmDealer$nonRehyAllContracts
     #Calculate repo CM as repo divided by non-rehypothecated SFT outflows
     cmDealer$USTextRP <- cmDealer$repo / cmDealer$nonRehyAllContracts


     #Output dealer-level CMs in a csv file
     write.csv(cmDealer, "cmDealer.csv", row.names = FALSE)
     
     return(cmDealer)
}

###############################
#### Calculate Average CMs ####
###############################

#This function calculates the cross-sectional average for the all-contract and repo CMs, which are shown in Figure 4 in the paper.
averageCm <- function(cmDealer) {

     #Take cross-sectional average across for all contracts CM, repo CM, and bilateral repo CM
     cmAverage <- as.data.frame(cmDealer %>% group_by(date) %>% summarise(Avg_USText = mean(USText, na.rm = TRUE), Avg_USTextRP = mean(USTextRP, na.rm = TRUE)))

     #Output average CMs into a csv file
     write.csv(cmAverage, "cmAverage.csv", row.names = FALSE)

     return(cmAverage)

}

    
######################
#### Main Program ####
######################

endDate <- as.Date("2022-03-31")

#Run function to clean FR2052a outflows data
outflows <- cleanFR2052Data(x=FR2052Raw[[1]], startDate = "2016-01-01", endDate = endDate)

#Run function to clean FR2052a inflows data
inflows <- cleanFR2052Data(x=FR2052Raw[[2]], startDate = "2016-01-01", endDate = endDate)

#Run function to clean FR2052a assets 
assets <- cleanFR2052Data(x=FR2052Raw[[3]], removeUnsettled = FALSE, startDate = "2016-01-01", endDate = endDate)

#Run function to aggregate and output total SFT volumes
aggSft(outflows,inflows,assets)

#Run function to calculate and output dealer-level CMs
cmDealer <- dealerLevelCm(outflows)

#Run function to calculate and output average CMs
cmAverage <- averageCm(cmDealer)
